using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class GIAODICHDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procGIAODICH_add]";
        private string _strSPUpdateName = "dbo.[procGIAODICH_update]";
        private string _strSPDeleteName = "dbo.[procGIAODICH_delete]";
        private string _strSPGetName = "dbo.[procGIAODICH_get]";
        private string _strSPGetAllName = "dbo.[procGIAODICH_getall]";
        private string _strSPGetPages = "dbo.[procGIAODICH_getpaged]";
        private string _strSPIsExist = "dbo.[procGIAODICH_isexist]";
        private string _strTableName = "[GIAODICH]";
        private string _strSPGetTransferOutName = "dbo.[procGIAODICH_gettransferout]";
        string _strSPGetCountName = "procGIAODICH_getcount";
        string _strSPGetByIndexName = "procGIAODICH_getindex";
        #endregion Local Variable
        string _strSPGetALLByCT = "procGIAODICH_getAllByCT";
        #region Method
        public GIAODICHInfo Get(
        String SOCT,
        String LOAICT,
        DateTime NGAYCT,
        ref string sErr)
        {
            GIAODICHInfo objEntr = new GIAODICHInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), NGAYCT);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (GIAODICHInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            GIAODICHInfo result = new GIAODICHInfo();
            result.SOCT = (dt.Rows[i][GIAODICHInfo.Field.SOCT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.SOCT.ToString()]));
            result.LOAICT = (dt.Rows[i][GIAODICHInfo.Field.LOAICT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.LOAICT.ToString()]));
            result.NGAYCT = (dt.Rows[i][GIAODICHInfo.Field.NGAYCT.ToString()] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dt.Rows[i][GIAODICHInfo.Field.NGAYCT.ToString()]));
            result.MAKH = (dt.Rows[i][GIAODICHInfo.Field.MAKH.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.MAKH.ToString()]));
            result.TIENHANG = (dt.Rows[i][GIAODICHInfo.Field.TIENHANG.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.TIENHANG.ToString()]));
            result.VAT = (dt.Rows[i][GIAODICHInfo.Field.VAT.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.VAT.ToString()]));
            result.PHIVC = (dt.Rows[i][GIAODICHInfo.Field.PHIVC.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.PHIVC.ToString()]));
            result.CHIETKHAU = (dt.Rows[i][GIAODICHInfo.Field.CHIETKHAU.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.CHIETKHAU.ToString()]));
            result.TONGTIEN = (dt.Rows[i][GIAODICHInfo.Field.TONGTIEN.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.TONGTIEN.ToString()]));
            result.TIENMAT = (dt.Rows[i][GIAODICHInfo.Field.TIENMAT.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.TIENMAT.ToString()]));
            result.CONGNO = (dt.Rows[i][GIAODICHInfo.Field.CONGNO.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.CONGNO.ToString()]));
            result.THUNGAY = (dt.Rows[i][GIAODICHInfo.Field.THUNGAY.ToString()] == DBNull.Value ? 0 : Convert.ToDouble(dt.Rows[i][GIAODICHInfo.Field.THUNGAY.ToString()]));
            result.LOAITIEN = (dt.Rows[i][GIAODICHInfo.Field.LOAITIEN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.LOAITIEN.ToString()]));
            result.NameKH = (dt.Rows[i][GIAODICHInfo.Field.NameKH.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.NameKH.ToString()]));
            result.DIACHIKH = (dt.Rows[i][GIAODICHInfo.Field.DIACHIKH.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.DIACHIKH.ToString()]));
            result.DIENTHOAI = (dt.Rows[i][GIAODICHInfo.Field.DIENTHOAI.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.DIENTHOAI.ToString()]));
            result.NOIDUNG = (dt.Rows[i][GIAODICHInfo.Field.NOIDUNG.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.NOIDUNG.ToString()]));
            result.TINHTRANG = (dt.Rows[i][GIAODICHInfo.Field.TINHTRANG.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.TINHTRANG.ToString()]));
            result.INHD = (dt.Rows[i][GIAODICHInfo.Field.INHD.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][GIAODICHInfo.Field.INHD.ToString()]));
            result.TAIKHOAN = (dt.Rows[i][GIAODICHInfo.Field.TAIKHOAN.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.TAIKHOAN.ToString()]));
            result.NGANHANG = (dt.Rows[i][GIAODICHInfo.Field.NGANHANG.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.NGANHANG.ToString()]));
            result.TINH_TP = (dt.Rows[i][GIAODICHInfo.Field.TINH_TP.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.TINH_TP.ToString()]));
            result.CREATEBY = (dt.Rows[i][GIAODICHInfo.Field.CREATEBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.CREATEBY.ToString()]));
            result.MODIFIEDBY = (dt.Rows[i][GIAODICHInfo.Field.MODIFIEDBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][GIAODICHInfo.Field.MODIFIEDBY.ToString()]));
            result.CREATEDATE = (dt.Rows[i][GIAODICHInfo.Field.CREATEDATE.ToString()] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dt.Rows[i][GIAODICHInfo.Field.CREATEDATE.ToString()]));
            result.MODIFIEDDATE = (dt.Rows[i][GIAODICHInfo.Field.MODIFIEDDATE.ToString()] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dt.Rows[i][GIAODICHInfo.Field.MODIFIEDDATE.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetByPos(
        int pos, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByIndexName);
            AddParameter("INX", pos);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public int GetCountRecord(
        ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPGetCountName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(GIAODICHInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), objEntr.SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), objEntr.LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), objEntr.NGAYCT);
            AddParameter(GIAODICHInfo.Field.MAKH.ToString(), objEntr.MAKH);
            AddParameter(GIAODICHInfo.Field.TIENHANG.ToString(), objEntr.TIENHANG);
            AddParameter(GIAODICHInfo.Field.VAT.ToString(), objEntr.VAT);
            AddParameter(GIAODICHInfo.Field.PHIVC.ToString(), objEntr.PHIVC);
            AddParameter(GIAODICHInfo.Field.CHIETKHAU.ToString(), objEntr.CHIETKHAU);
            AddParameter(GIAODICHInfo.Field.TONGTIEN.ToString(), objEntr.TONGTIEN);
            AddParameter(GIAODICHInfo.Field.TIENMAT.ToString(), objEntr.TIENMAT);
            AddParameter(GIAODICHInfo.Field.CONGNO.ToString(), objEntr.CONGNO);
            AddParameter(GIAODICHInfo.Field.THUNGAY.ToString(), objEntr.THUNGAY);
            AddParameter(GIAODICHInfo.Field.LOAITIEN.ToString(), objEntr.LOAITIEN);
            AddParameter(GIAODICHInfo.Field.NameKH.ToString(), objEntr.NameKH);
            AddParameter(GIAODICHInfo.Field.DIACHIKH.ToString(), objEntr.DIACHIKH);
            AddParameter(GIAODICHInfo.Field.DIENTHOAI.ToString(), objEntr.DIENTHOAI);
            AddParameter(GIAODICHInfo.Field.NOIDUNG.ToString(), objEntr.NOIDUNG);
            AddParameter(GIAODICHInfo.Field.TINHTRANG.ToString(), objEntr.TINHTRANG);
            AddParameter(GIAODICHInfo.Field.INHD.ToString(), objEntr.INHD);
            AddParameter(GIAODICHInfo.Field.TAIKHOAN.ToString(), objEntr.TAIKHOAN);
            AddParameter(GIAODICHInfo.Field.NGANHANG.ToString(), objEntr.NGANHANG);
            AddParameter(GIAODICHInfo.Field.TINH_TP.ToString(), objEntr.TINH_TP);
            AddParameter(GIAODICHInfo.Field.CREATEBY.ToString(), objEntr.CREATEBY);
            AddParameter(GIAODICHInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(GIAODICHInfo.Field.CREATEDATE.ToString(), objEntr.CREATEDATE);
            AddParameter(GIAODICHInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(GIAODICHInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), objEntr.SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), objEntr.LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), objEntr.NGAYCT);
            AddParameter(GIAODICHInfo.Field.MAKH.ToString(), objEntr.MAKH);
            AddParameter(GIAODICHInfo.Field.TIENHANG.ToString(), objEntr.TIENHANG);
            AddParameter(GIAODICHInfo.Field.VAT.ToString(), objEntr.VAT);
            AddParameter(GIAODICHInfo.Field.PHIVC.ToString(), objEntr.PHIVC);
            AddParameter(GIAODICHInfo.Field.CHIETKHAU.ToString(), objEntr.CHIETKHAU);
            AddParameter(GIAODICHInfo.Field.TONGTIEN.ToString(), objEntr.TONGTIEN);
            AddParameter(GIAODICHInfo.Field.TIENMAT.ToString(), objEntr.TIENMAT);
            AddParameter(GIAODICHInfo.Field.CONGNO.ToString(), objEntr.CONGNO);
            AddParameter(GIAODICHInfo.Field.THUNGAY.ToString(), objEntr.THUNGAY);
            AddParameter(GIAODICHInfo.Field.LOAITIEN.ToString(), objEntr.LOAITIEN);
            AddParameter(GIAODICHInfo.Field.NameKH.ToString(), objEntr.NameKH);
            AddParameter(GIAODICHInfo.Field.DIACHIKH.ToString(), objEntr.DIACHIKH);
            AddParameter(GIAODICHInfo.Field.DIENTHOAI.ToString(), objEntr.DIENTHOAI);
            AddParameter(GIAODICHInfo.Field.NOIDUNG.ToString(), objEntr.NOIDUNG);
            AddParameter(GIAODICHInfo.Field.TINHTRANG.ToString(), objEntr.TINHTRANG);
            AddParameter(GIAODICHInfo.Field.INHD.ToString(), objEntr.INHD);
            AddParameter(GIAODICHInfo.Field.TAIKHOAN.ToString(), objEntr.TAIKHOAN);
            AddParameter(GIAODICHInfo.Field.NGANHANG.ToString(), objEntr.NGANHANG);
            AddParameter(GIAODICHInfo.Field.TINH_TP.ToString(), objEntr.TINH_TP);
            AddParameter(GIAODICHInfo.Field.CREATEBY.ToString(), objEntr.CREATEBY);
            AddParameter(GIAODICHInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(GIAODICHInfo.Field.CREATEDATE.ToString(), objEntr.CREATEDATE);
            AddParameter(GIAODICHInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        String SOCT,
        String LOAICT,
        DateTime NGAYCT
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), NGAYCT);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(GIAODICHInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        String SOCT,
        String LOAICT,
        DateTime NGAYCT
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), NGAYCT);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(GIAODICHInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

        public DataTable GetALLByCT(
        String SOCT,
        String LOAICT,
        DateTime NGAYCT,
        ref string sErr)
        {
            GIAODICHInfo objEntr = new GIAODICHInfo();
            InitConnect();
            InitSPCommand(_strSPGetALLByCT);
            AddParameter(GIAODICHInfo.Field.SOCT.ToString(), SOCT);
            AddParameter(GIAODICHInfo.Field.LOAICT.ToString(), LOAICT);
            AddParameter(GIAODICHInfo.Field.NGAYCT.ToString(), NGAYCT);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            //if (list.Rows.Count > 0)
            //    objEntr = new GIAODICHInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
